package healthcaresystem;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import java.io.*;
import java.sql.*;
import java.util.*;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JOptionPane;

 class Database1 {

    private String driver;  // = "oracle.jdbc.driver.OracleDriver";
    private String url;     // = "jdbc:oracle:thin:0 E10803:1521:orcl";
    private String username;//= "system";
    private String password;// = "tiger";

    private String query;
    private Connection conn;
    private Statement s;
    private PreparedStatement ps;
    private ResultSet rs;

    //load property file by using properties class:
    public void initParam(String paramFile) throws Exception {
        Properties props = new Properties();
        props.load(new FileInputStream(paramFile));
        driver = props.getProperty("driver");
        url = props.getProperty("url");
        username = props.getProperty("user");
        password = props.getProperty("pass");
    }//tested

    public ResultSet getResultSet() {
        return rs;
    }//get ResultSet object after SQL statement is executed

    public boolean connect2Oracle() { //throws Exception 
        try {
            Class.forName(driver);
            conn = DriverManager.getConnection(url, username, password);
            conn.setAutoCommit(true);
            //System.out.println("Connected to database.");
            return true;
        } catch (Exception e) {
            //System.out.println(e.getMessage());
            return false;
        }
    }//tested

    //for DDL commands: CREATE, DROP
    public void executeDDL(String sqlFile) throws Exception {
        String sql;
        Scanner inFile = new Scanner(new FileReader(sqlFile));

        connect2Oracle();  //connect to database
        s = conn.createStatement();

        while (inFile.hasNextLine()) {
            sql = inFile.nextLine();
            System.out.println(sql);//test
            try {
                s.executeUpdate(sql);
            } catch (Exception e) {
                System.out.println("DDL Error:" + e.getMessage());
            }
        }
        inFile.close();
        conn.close();
    }//tested

    /**
     *
     * @param tableName name of the table to which insertion occurred
     * @param values values of the table columns
     * @return (1) the row count for SQL DML statements; (2) 0 for SQL
     * statements that return nothing; (3) -1 for error.
     */
    public int executeDML(String sql, Object... values) throws Exception {
//        String sql;
        int rows;
        int num_val = values.length;

        //generate the insertion sql statement:
//        sql = "INSERT INTO " + tableName + " VALUES(";
//        for (int i = 0; i < num_val; i++) {
//            sql += i == (num_val - 1) ? "?)" : "?,";
//        }
        System.out.println(sql);//test

        //execute:
//        try {
        connect2Oracle();  //connect to database
        ps = conn.prepareStatement(sql);

        //get column values:
        for (int i = 0; i < num_val; i++) {
            ps.setObject(i + 1, values[i]);
        }

        rows = ps.executeUpdate();
        conn.close();
        return rows;

//        } catch (Exception e) {
//            System.out.println("Error:" + e.getMessage());
//            return -1;
//        }
    }//tested

    /**
     *
     * @param tableName table name
     * @param colName column name (WHERE clause)
     * @param colVal column value (WHERE clause)
     * @return (1) the row count for SQL DML statements; (2) 0 for SQL
     * statements that return nothing; (3) -1 for error.
     */
    public int delete(String tableName, String colName, String colVal) {
        String sql;
        int rows;

        //generate the insertion sql statement:
        sql = "DELETE FROM " + tableName + " WHERE " + colName + "=?";

        System.out.println(sql);//test

        //execute:
        try {
            connect2Oracle();  //connect to database
            ps = conn.prepareStatement(sql);

            ps.setObject(1, colVal);
            rows = ps.executeUpdate();
            conn.close();
            return rows;

        } catch (Exception e) {
            System.out.println("Error:" + e.getMessage());
            return -1;
        }
    }//tested

    /**
     *
     * @param sql SELECT statement (pre-compling)
     * @param values used to replace '?' in String sql
     * @return ResultSet
     * @throws Exception
     */
    public ResultSet executePre_Query(String sql, String... values) {
//        String sql;
//        String[] result=new String[cols];
//        int count=0;
        try {
            connect2Oracle();  //connect to database
            ps = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

            //execute:
            for (int i = 0; i < values.length; i++) {
                ps.setString(i + 1, values[i]);
            }
            rs = ps.executeQuery();
            System.out.println(sql);//test
            JOptionPane.showMessageDialog(null, "Database operation is executed successfully.",
                    "Database Operation", JOptionPane.INFORMATION_MESSAGE);
            return rs;
            //convert ResultSet into a String array:
//            while (rs.next()) {
//                count++;
//                for (int i = 0; i < cols; i++) {
//                    result[i] = rs.getString(i + 1);
//                }
//            } //only one row
//            rs.close();
//            c.close();
//            if (count==0){//no row selected
//                return null;
//            }
//            return result;
//
        } catch (Exception e) {
//            System.out.println("Error:" + e.getMessage());
            JOptionPane.showMessageDialog(null, "Error: " + e.getMessage(),
                    "Database Operation", JOptionPane.ERROR_MESSAGE);
            return null;
        }
    }//tested

    /**
     *
     * @param tableName table name
     * @param colName array of column names to be updated
     * @param colValue array of column values
     * @param keyName column name used for WHERE clause
     * @param keyValue column value used for WHERE clause
     * @return (1) the row count for SQL DML statements; (2) 0 for SQL
     * statements that return nothing; (3) -1 for error.
     */
    public int update(String tableName, String[] colName, Object[] colValue,
            String keyName, String keyValue) {
        String sql;
        int rows;
        int num_col = colName.length;

        //generate the insertion sql statement:
        sql = "UPDATE " + tableName + " SET ";
        for (int i = 0; i < num_col; i++) {
            sql += colName[i] + "=" + (i == (num_col - 1) ? "? " : "?, ");
        }

        sql += "WHERE " + keyName + " =?";
        System.out.println(sql);//test

        //execute:
        try {
            connect2Oracle();  //connect to database
            ps = conn.prepareStatement(sql);

//            System.out.println("num_col=" + num_col); //didn't get here
            //get column values:
            for (int i = 0; i < num_col; i++) {
                ps.setObject(i + 1, colValue[i]);
            }
            ps.setObject(num_col + 1, keyValue);

            rows = ps.executeUpdate();

//            System.out.println("I'm here 2"); //didn't get here
            conn.close();
//            System.out.println("rows=" + rows);//test
            return rows;

        } catch (Exception e) {
            System.out.println("Error:" + e.getMessage());
            return -1;
        }
    }//tested

    /**
     *
     * @param sql SELECT statement
     * @return ResultSet
     */
    public ResultSet executeQuery(String sql) {

        try {
//            connect2Oracle();  //connect to database
//            System.out.println("connected");
            s = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

            //execute:
            rs = s.executeQuery(sql);
            //System.out.println("SQL executed.");
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, "Error: " + e.getMessage(),
                    "Database Operation", JOptionPane.ERROR_MESSAGE);
            rs = null;
        }
        return rs;
    }

    public boolean close() {
        try {
            conn.close();
            //System.out.println("Connection closed.");
            return true;
        } catch (Exception e) {
//            JOptionPane.showMessageDialog(null, "Error: " + e.getMessage(),
//                    "Close Database Operation", JOptionPane.ERROR_MESSAGE);
            System.out.println(e.getMessage());
            return false;
        }
    }

    public boolean executeDML(String sql) {
        return true;
    }

    public String MD5(String md5) {
        try {
            java.security.MessageDigest md = java.security.MessageDigest.getInstance("MD5");
            byte[] array = md.digest(md5.getBytes());
            StringBuffer sb = new StringBuffer();
            for (int i = 0; i < array.length; ++i) {
                sb.append(Integer.toHexString((array[i] & 0xFF) | 0x100).substring(1, 3));
            }
            return sb.toString();
        } catch (java.security.NoSuchAlgorithmException e) {
        }
        return null;
    }
}

//singleton: to avoid this class to be created many times
final class C3P0DataSource {

    private static C3P0DataSource instance;
    public  ComboPooledDataSource ds; //pooled data source

    private final int MAX_POOL_SIZE = 40;
    private final int MIN_POOL_SIZE = 2;
    private final int INIT_POOL_SIZE = 10;
    private final int MAX_STATEMENTS = 180;

    private String driver;  // = "oracle.jdbc.driver.OracleDriver";
    private String url;     // = "jdbc:oracle:thin:0 E10803:1521:orcl";
    private String username;//= "system";
    private String password;// = "tiger";

    //private constructor
    private C3P0DataSource() {
        if (ds == null) {
            ds = new ComboPooledDataSource();  //create pool

            try {
                initParam("mysql.ini");        //get settings from .ini file.
                connect2DBPool();              //set pool parameters
            } catch (Exception e) {
                System.out.println(e.getMessage());
            }
        }
    }

    //load property file by using properties class:
    private void initParam(String paramFile) throws Exception {
        Properties props = new Properties();
        props.load(new FileInputStream(paramFile));
        driver = props.getProperty("driver");
        url = props.getProperty("url");
        username = props.getProperty("user");
        password = props.getProperty("pass");
    }//tested

    /**
     * Create and set the C3P0 connection pool used to get DB connections.
     *
     * @return true if succeed, false if fail.
     */
    private boolean connect2DBPool() {
        try {

            ds.setDriverClass(driver);
            ds.setJdbcUrl(url);
            ds.setUser(username);
            ds.setPassword(password);
            ds.setMaxPoolSize(MAX_POOL_SIZE);
            ds.setMinPoolSize(MIN_POOL_SIZE);
            ds.setInitialPoolSize(INIT_POOL_SIZE);
            ds.setMaxStatements(MAX_STATEMENTS);
            return true;

        } catch (Exception e) {
            System.out.println(e.getMessage());
            return false;
        }
    }

    public static synchronized C3P0DataSource getInstance() {
        if (instance == null) {
            instance = new C3P0DataSource();
        }
        return instance;
    }
}

public class Database {
    private Connection conn;
    private String query;
    private Statement s;
    private PreparedStatement ps;
    private ResultSet rs;
    
    public Database(Connection conn) throws SQLException{
        this.conn = C3P0DataSource.getInstance().ds.getConnection();
    }
    
    public ResultSet executeQuery(String sql) {

        try {
            s = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

            //execute:
            rs = s.executeQuery(sql);
            //System.out.println("SQL executed.");
        } catch (SQLException ex) {
            Logger.getLogger(frmMain.class.getName()).log(Level.SEVERE, null, ex);
            rs = null;
        }
        return rs;
    }

    public ResultSet getRs() {
        return rs;
    }
    
    
}